﻿CREATE PROCEDURE [dbo].[proc_Finance_Report_GetList]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10)
	)
AS
/*Begin
	Select
	c.Id,
	c.CompanyName,
	IsNull(YesterdayCashSum,0) As YesterdayCashSum,--昨日现金总计
	IsNull(YesterdayCashSum,0) As YesterdayBankSum,--昨日银行总计
	ISNULL(CashIn,0) As CashIn,--现金收入总计
	ISNULL(CashOut,0) As CashOut,--现金支出总计
	ISNULL(BankIn,0) As BankIn,--银行收入总计
	ISNULL(BankOut,0) As BankOut,--银行支出总计
	ISNULL(XZML,0) As XZML,--新增毛利
	ISNULL(XFML,0) As XFML,--续费毛利
	ISNULL(XZYS,0) As XZYS,--新增应收
	ISNULL(CostPriceAll,0) As CostPriceAll,--新增项目成本
	ISNULL(CostPriceFinish,0) As CostPriceFinish,--已完成项目成本
	ISNULL(WKSR,0) As WKSR--尾款收入
	From Finance_Report c Where (@CompanyId=0 Or c.Id=@CompanyId) And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)
End*/
Begin
	Select
	c.Id,
	c.CompanyName,
	(
		ISNULL((Select Sum(InitialMoney) From Finance_Bank Where TypeId=1 And CompanyId=c.Id),0)+
		ISNULL((Select Sum(CashIn)-Sum(CashOut) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0)
	) As TodayCashSum,
	(
		ISNULL((Select Sum(InitialMoney) From Finance_Bank Where TypeId=2 And CompanyId=c.Id),0)+
		ISNULL((Select Sum(BankIn)-Sum(BankOut) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0)
	) As TodayBankSum,
	ISNULL((Select Sum(XZML) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As XZML,
	ISNULL((Select Sum(XFML) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As XFML,
	ISNULL((Select Sum(XZYS) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As XZYS,
	ISNULL((Select Sum(CostPriceAll) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As CostPriceAll,
	ISNULL((Select Sum(CostPriceFinish) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As CostPriceFinish,
	ISNULL((Select Sum(WKSR) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As WKSR,
	ISNULL((Select Sum(CashOut+BankOut) From Finance_Report Where CompanyId=c.Id And (Convert(nchar(10),ShengChengDate,120) between @StartDate And @EndDate)),0) As ZCJE
	From Company c Where (@CompanyId=0 Or c.Id=@CompanyId)
End
	RETURN
